import requests
import re
import time
import lxml.html
import pymysql

etree = lxml.html.etree

class ZhiCityProTwo:

    #初始化函数
    def __init__(self):
        self.header = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.111 Safari/537.36',
            'Content-type': 'text/html',
            'charset': 'utf-8'}


    #获取文件信息
    def get_project(self):

        conn = pymysql.connect(host='192.168.187.130', user='root', passwd='123456', db='homework')
        cur = conn.cursor()

        cur.execute("select pid from project")
        lis = cur.fetchall()
        i = 0

        for ll in lis:

            wholeurl = "http://www.taskcity.com/projects/"+str(ll)[1:-2]
            #print(wholeurl)

            mask = int(str(ll)[1:-2])
            print("mask:"+str(mask))

            response = requests.get(url=wholeurl, headers=self.header)
            response.encoding = "utf-8"

            if response.status_code == 200:

                html = etree.HTML(response.text)

                prize = str(html.xpath("/html/body/div[1]/div/div[7]/div[1]/div[1]/div[2]/b/text()"))[20:-18]

                # 两步走，先去除千分号，再字符串转数字
                num = re.findall(r'\d+', prize.replace(',', ''))
                print("num:"+str(num))

                if len(num) >=2:
                    uprize = int(num[0])
                    downprize = int(num[1])
                    midprize = (uprize+downprize)/2
                elif len(num) == 1:
                    uprize = int(num[0])
                    downprize = int(num[0])
                    midprize = (uprize+downprize)/2
                else:
                    uprize = 0
                    downprize = 0
                    midprize = 0

                if len(num) == 0:
                    k = 2
                else:
                    k = 3

                path = "/html/body/div[1]/div/div[7]/div[1]/div[1]/div[%s]/b/text()" % (k)
                daylist = str(html.xpath(path))
                day = re.findall(r'\d+',str(daylist))
                #print(str(daylist))
                if len(day) == 0:
                    days = 0
                else:
                    days = int(day[0])


                if days > 0 and midprize > 0:
                    averageprofit = float(midprize)/float(days)
                    sql = "update project set uprize = %d, downprize = %d, midprize = %d, averageprofit = %f where pid = %d" % (
                        uprize,downprize,midprize,averageprofit,mask)
                    try:
                        print(sql)
                        cur.execute(sql)
                        conn.commit()
                    except:
                        print("update prize error!")


                try:
                    sql = "update project set time = %d where pid = %d" % (days,mask)
                    cur.execute(sql)
                    conn.commit()
                    print(sql)
                except:
                    print("error:update time")


                llis = html.xpath('/html/body/div[1]/div//table[@class = "bid_table "]//tr//td/a[2]/@href')

                for ll in llis:
                    #print("ll:"+str(ll))
                    uid = re.findall("/(.*?)-",str(ll)[3:])

                    print("uid:"+str(uid))
                    try:
                        testsql = "select count(uid) from bid where uid = %s and pid = %d limit 1" % (uid[0],mask)
                        print(testsql)
                        cur.execute(testsql)
                        result = cur.fetchone()
                        #print("result:"+str(result))
                        if result[0] == 0:
                            sql = "insert into bid(uid,pid) values (%s,%d) " % (uid[0],mask)
                            print(sql)
                            cur.execute(sql)
                            conn.commit()
                    except:
                        print("bid error")

                lis = html.xpath('//div[@class ="pull-left space-right-30"]//a[@style="margin-left:5px"]/text()')

                for ll in lis:
                    try:
                        testsql = "select count(pid) from pskill where pid = %d and skillname = '%s' limit 1" % (mask,ll)
                        print(testsql)
                        cur.execute(testsql)
                        result = cur.fetchone()
                        if result[0] == 0:
                            sql = "insert into pskill(pid,skillname) values(%d,'%s') " % (mask, ll)
                            print(sql)
                            cur.execute(sql)
                            conn.commit()
                    except:
                        print("failed to insert project skill!")

            i+=1
            if i % 10 == 0:
                time.sleep(30)

        cur.close()
        conn.close()

if __name__ in "__main__":
    cls = ZhiCityProTwo()
    cls.get_project()
